using SunshineAirlineTest1.DataAccess;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace SunshineAirlineTest1
{
    public partial class SearchFlights : Form
    {
        private string sqltmp;
        private DataTable dttmp;

        private static DataTable dt_dep;
        private static DataTable dt_arr;

        private Dictionary<string, object> flightInfo;


        public SearchFlights()
        {
            InitializeComponent();
        }

        private void SearchFlights_Load(object sender, EventArgs e)
        {
            BindComboBoxData();
        }

        private void btnSearch_Click(object sender, EventArgs e)
        {
            if (cbDEPct.SelectedItem == null || string.IsNullOrEmpty(cbDEPct.SelectedValue.ToString())) { MessageBox.Show("No Such Flights."); return; }
            if (cbARRct.SelectedItem == null || string.IsNullOrEmpty(cbARRct.SelectedValue.ToString())) { MessageBox.Show("No Such Flights."); return; }
            DataTable flights = new DataTable();
            if (!searchFlights(out flights)) { MessageBox.Show("No Such Flights."); return; }
            List<Route> RouteList = SQLHelper.FillModel<Route>(flights);
            
        }

        #region Query
        private bool searchFlights(out DataTable dtout)
        {
            sqltmp = string.Format("select * from Route where DepartureAirportIATA in ({0})", cbDEPct.SelectedValue.ToString());
            dt_dep = SQLHelper.ExecuteDataTable(sqltmp);
            sqltmp = string.Format("select * from Route where ArrivalAirportIATA in ({0})", cbARRct.SelectedValue.ToString());
            dt_arr = SQLHelper.ExecuteDataTable(sqltmp);
            if (dt_dep.Rows.Count > 0 && dt_arr.Rows.Count > 0)
            {
                IEnumerable<DataRow> QJJ = dt_dep.AsEnumerable().Intersect(dt_arr.AsEnumerable(), DataRowComparer.Default);
                if (QJJ.Any())
                {
                    dtout = QJJ.CopyToDataTable();
                    return dtout.Rows.Count > 0;
                }
                //var query =
                //from dep in dt_dep.AsEnumerable()
                //join arr in dt_arr.AsEnumerable()
                //on dep.Field<Int32>("ArrivalAirportIATA") equals arr.Field<Int32>("DepartureAirportIATA")
                //select dep.ItemArray.Concat(arr.ItemArray.Skip(1));
                DataSet dsContainer = new DataSet();
                dsContainer.Tables.Add(dt_dep);
                dsContainer.Tables.Add(dt_arr);
                DataRelation relTransfer = new DataRelation("Transfer", new DataColumn[] { dt_dep.Columns["ArrivalAirportIATA"] }, new DataColumn[] { dt_arr.Columns["DepartureAirportIATA"] });
                dsContainer.Relations.Add(relTransfer);

            }
            dtout = new DataTable();
            return false;
        }

        private List<Dictionary<string,object>> FillFlightInfo(List<Route> RouteList)
        {
            List<Dictionary<string, object>> rtnList = new List<Dictionary<string, object>>();
            foreach (Route r in RouteList)
            {
                sqltmp = "select * from Airport where IATACode='{0}'";
                r.DepartureAirport = SQLHelper.ReaderToModel<Airport>(SQLHelper.ExecuteReader(string.Format(sqltmp, r.DepartureAirportIATA)));
                r.ArrivalAirport = SQLHelper.ReaderToModel<Airport>(SQLHelper.ExecuteReader(string.Format(sqltmp, r.ArrivalAirportIATA)));
                sqltmp = "select * from City where CityCode='{0}'";
                r.DepartureAirport.City = SQLHelper.ReaderToModel<City>(SQLHelper.ExecuteReader(string.Format(sqltmp, r.DepartureAirport.CityCode)));
                r.ArrivalAirport.City = SQLHelper.ReaderToModel<City>(SQLHelper.ExecuteReader(string.Format(sqltmp, r.ArrivalAirport.CityCode)));
                CreateDictionary();
                flightInfo["DepartureCity"] = r.DepartureAirport.City.CityName + "/" + r.DepartureAirportIATA;
                flightInfo["ArrivalCity"] = r.ArrivalAirport.City.CityName + "/" + r.ArrivalAirportIATA;
            }
            return rtnList;
        }

        #endregion

        #region Tools
        private void BindComboBoxData()
        {
            sqltmp = "select CityName,stuff((select ','+char(39)+IATACode+char(39) from Airport a where c.CityCode=a.CityCode for xml path('')),1,1,'') as IATAs from City c order by CityName";
            dttmp = SQLHelper.ExecuteDataTable(sqltmp);
            if (dttmp.Rows.Count > 0)
            {
                CommonHelper.BindComboBoxList(cbDEPct, dttmp, "IATAs", "CityName");
                CommonHelper.BindComboBoxList(cbARRct, dttmp.Copy(), "IATAs", "CityName");
            }
            sqltmp = "select CabinTypeId,CabinTypeName from CabinType";
            dttmp = SQLHelper.ExecuteDataTable(sqltmp);
            if (dttmp.Rows.Count > 0) CommonHelper.BindComboBoxList(cbCabin, dttmp, "CabinTypeId", "CabinTypeName");
        }

        private void CreateDictionary()
        {
            flightInfo = new Dictionary<string, object>();
            string[] keys = new string[] {
                "TicketPrice","CabinType","FlightNum","DelayRate",
                "DepartureCity","ArrivalCity","DepatrureTime","ArrivalTime",
                "FlightType","TotalTime","RemainingTickets"
            };
            foreach (string key in keys)
            {
                flightInfo[key] = "";
            }
        }
        #endregion
    }
}
